#encoding: utf-8

load 'wechat_api.rb'
load 'tutem_utils.rb'
load 'send-sms.rb'

#图腾纾正上市后給所有购买过小儿拾及大豆肽的用户发送积分消息

def evt230220TTSZ

    uid = 1
    code = 'EVT-230220-TTSZ-TEST'
    bonus_points = 10.99
    channel = '客户服务部'
    brief = "大豆肽及小儿拾产品升级"
    title = '图腾纾正'
    mark = '点击进公众号领取满900送100新品礼券'

    rec = {}
    rec.store 'uid', uid
    rec.store 'code', code
    rec.store 'bonus', bonus_points
    rec.store 'channel', channel #这里是模板消息的交易门店
    rec.store 'brief', brief #这里是模板消息的服务内容
    rec.store 'title', title #这里是流水的mark
    rec.store 'mark', mark #这里是流水的mark

    bill_id = award_points_to_user rec

end
#evt230220TTSZ


#event={'name':'仲秋献礼','goods':'赠送忆家合民国月饼1盒','code':'ec20220902','rules':'活动期间实际支付满1500元的老用户','where':'where xxx'}
def qualify_event_to_orders event

    name = event['name']
    code = event['code']
    goods = event['goods']
    rules = event['rules']
    event_start = event['event_start']
    event_end = event['event_end']

    rx_sql = event['rx_sql'] #入选活动条件，例如，活动前已满100元消费
    mz_sql = event['mz_sql'] #满足活动条件，例如，活动期间实际购满1500元

    puts "qualify event #{name}"

    rx_sql = "select sum(actual_paid) - sum(refunded_amount) as spend_money, uid from tutem_mall.eb_pickup_book where pay_type = 'weixin' and update_time < #{event_start} group by uid"

    rx_uids = []
    queryRds(rx_sql).each do |q|
        uid = q['uid']
        spend_money = q['spend_money']
        rx_uids += [q['uid']] if spend_money >= 100.00
    end

    rx_where = " and uid in (#{rx_uids.join(',')})"
    rx_where = "" if rx_uids.size == 0
    mz_sql = "select sum(actual_paid) as total_paid, sum(refunded_amount) as total_refunded, uid from tutem_mall.eb_pickup_book where pay_type = 'weixin' and update_time >= #{event_start} and update_time < #{event_end} #{rx_where} group by uid"

    mz_userList = {}
    mz_uids=[]
    queryRds(mz_sql).each do |q|
        uid = q['uid']
        actual_spend = q['total_paid'] - q['total_refunded']
        if actual_spend >= 1500.00
            mz_userList.store  uid, actual_spend.to_f.round(2)
            mz_uids += [uid]
            #puts "uid##{uid} spend #{actual_spend.to_f.round(2)}"
        end
    end

    #已经打过标记的用户
    md_sql = "select uid from tutem_mall.eb_store_order where pay_time > #{event_start} and pay_time < #{event_end} and notification like '%#{code}%'"
    marked_uids=[]
    (queryRds(md_sql)).each do |q|
        uid = q['uid']
        marked_uids += [uid]
    end
    marked_uids = marked_uids.uniq

    dbj_uids = mz_uids - marked_uids #新的满足条件但尚未打标记的

    dbj_where = " and o.uid in (#{dbj_uids.join(',')})"
    dbj_where = " and o.uid = 999999" if dbj_uids.size == 0 #如果无用户则不循环
    dbj_sql = "select max(o.id) as id, o.real_name, o.user_phone, o.user_address, o.uid, o.notification, o.remark, w.openid, w.subscribe
        from tutem_mall.eb_store_order o
        left join tutem_mall.eb_wechat_user w on (o.uid = w.uid)
        where o.paid = 1 and o.pay_time >= #{event_start} and o.pay_time < #{event_end} #{dbj_where} group by o.uid"

    idx = 0
    queryRds(dbj_sql).each do |q|

        order_id = q['id']
        uid = q['uid']
        openid = q['openid'].to_s
        subscribe = q['subscribe']

        print '.'
        idx += 1

        wm_noticed = false
        if openid != '' && subscribe == 1

            user_name = q['real_name']

            template_id = 'EjDB1-4Jh0_ROs5-CVghAAg7SxA25n3GEWeoVqRct0E'
            url = 'https://www.totembio.cn/'
            miniprogram = {appid: "wx1b441d1a37b278e1", pagepath:  "pages/user/user" }
            first = "\n#{user_name}，您已满足#{name}活动条件"
            keyword1 = Time.now.strftime('%Y-%m-%d %H:%M:%S')
            keyword2 = rules + "，当前#{mz_userList[uid]}"
            keyword3 = goods
            remark = '赠品从定制工坊直接发不随订单，详情请咨询客服或留意公众号消息'

            #openid = 'oqlyX6deLDP2EWlk0qZgWCU7wH6I'
            rec = {
                touser: openid, template_id: template_id, url: url, miniprogram: miniprogram,
                data: {
                    first:  { value:  "#{first}"},                            #抬头,这一行加了颜色也不起作用
                    keyword1:  { value:  "#{keyword1}", color:  '#888888' },  #反馈时间
                    keyword2:  { value:  "#{keyword2}", color:  '#173177' },  #反馈内容
                    keyword3:  { value:  "#{keyword3}", color:  '#88b04b' },  #回复内容
                    remark:  { value: "#{remark}", color:  '#888888' },       #备注
                },
            }

            res = wechat_post :sendTemplateMessage, rec
            wm_noticed = true

        end

        mark_down = "## 用户U##{uid} 满足#{name}活动赠送\n"
        mark_down += "当前订单：##{order_id}\n"
        mark_down += "期间实付：#{mz_userList[uid]}\n"
        mark_down += wm_noticed ? "公众号模板消息已发\n" : "公众号模板消息未发\n"
        mark_down += "> #{goods}\n"
        mark_down += "### #{q['user_name']} #{q['user_phone']}\n"
        mark_down += "### #{q['user_address']}\n" if q['user_address'].to_s != ''

        send_message_to_bot 'WWBOT_XT', mark_down, ['@all']
        #puts mark_down

        nots = q['notification'].split(' ')
        nots += [code] #already notified with wework
        noti = nots.uniq.join(' ')

        remark = "[OMS2] #{goods}（#{name}）\n" + q['remark'].to_s
        sqlu = "update tutem_mall.eb_store_order set notification = '#{@rds_tt.escape noti}', remark = '#{@rds.escape remark}' where id = #{q['id']};"
        #puts sqlu

        queryRds sqlu

    end

    puts "\ndone. #{idx}"

end

def mark_event_20220902

    event = {}
    event.store 'name', '仲秋献礼'
    event.store 'goods', '赠忆家合民国月饼1盒'
    event.store 'code', 'ec20220902'
    event.store 'rules', '老用户0902-0910期间实付金额达到1500元'

    event_start = Time.parse('2022-09-02').to_i
    event_end = Time.parse('2022-09-11').to_i
    event.store 'event_start', event_start
    event.store 'event_end', event_end

    qualify_event_to_orders event

end

def sendEventPointsNotice ( openid='oqlyX6deLDP2EWlk0qZgWCU7wH6I',
        operation = '悦启新运活动兑换',
        message = '0.0元',
        change = '20.24',
        balance = '点击查看/使用',
        url='https://totemlife.cn/apps/loyalty/lottery?code=LTR-2024-02-01',
        miniprogram={} # { appid: "wx1b441d1a37b278e1", pagepath:  "pages/user_integral/index" },
    )
    touser = openid
    template_id = 'NFnG6uAdy9LP-t419odIUq05j3Y8H69UN-GTK_akQ9Y'
    data = {
        first:  '',
        keyword1: { value: '图腾生命医学小程序' },
        keyword2:  { value:   "#{message}"},
        keyword3:  { value:   "#{operation}"},
        keyword4:  { value:  "#{change.to_i}金豆"},
        keyword5:  { value:  "#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"},
        remark:  '',
    }
    res =  send_mp_ts touser, template_id, url, miniprogram, data
    ap res
    return true if res['errcode'] == 0
    return false
end

def awardPointsToUser rec #returned with bill_id

    bill_id = 0
    uid = rec['uid']
    code = rec['code']
    bonus = rec['bonus']
    channel = rec['channel']
    brief = rec['brief']
    title = rec['title']
    mark = rec['mark']
    weworkNotice = rec['weworkNotice'].nil? ? true : rec['weworkNotice']
    mpNotice = rec['mpNotice'].nil? ? true : rec['mpNotice']
    mpsRequired = rec['mpsRequired'].nil? ? true : rec['mpsRequired'] #必须要求关注公众号, 默认为必须

    msg = "[#{code}] awarding U##{uid} with #{bonus} points #{mark}"
    puts msg

    openid = ''
    user_name = ''
    user_phone = ''
    p_balance = 0.00
    subscribe = 0

    sql = "select u.real_name, u.phone, w.subscribe, w.openid, u.integral as p_balance from tutem_mall.eb_user u left join tutem_mall.eb_wechat_user w on (u.uid = w.uid) where u.uid = #{uid};"
    (queryRds(sql)).each do |q|
        openid =  q['openid']
        p_balance = q['p_balance']
        subscribe =q['subscribe']
        user_name = q['real_name'].to_s
        user_phone = q['phone'].to_s
        break
    end
    if ( openid.to_s.empty? || subscribe == 0 ) && mpsRequired
        err = "[ABORT] openid is nil or not subscribed(#{subscribe}) when #{msg}"
        puts err
        send_message_to_bot 'WWBOT_XT', err, ['@all'] if weworkNotice
        return 0
    end

    awarded = 0
    sql = "select count(*) as awarded from tutem_mall.eb_user_bill where uid = #{uid} and link_id = '#{code}'"
    (queryRds(sql)).each do |q|
        awarded = q['awarded']
        break
    end
    if awarded > 0
        err = "[ABORT] already awarded for #{awarded} times when #{msg}"
        puts err
        send_message_to_bot 'WWBOT_XT', err, ['@all'] if weworkNotice
        return 0
    end

    now = Time.now.to_i
    sqls = []
    usql = "update tutem_mall.eb_user set integral = integral + #{bonus} where uid = #{uid};"
    sqls += [usql]
    usql = "INSERT INTO tutem_mall.eb_user_bill (`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) 
      SELECT #{uid}, '#{code}', 1, '#{title}', 'integral', 'system_add', #{bonus}, integral, '#{mark}', #{now}, 1, 0 from tutem_mall.eb_user where uid = #{uid}"
    sqls += [usql]

    commit = commitTrans sqls

    if commit

        isql = "select id from tutem_mall.eb_user_bill where uid = #{uid} and link_id = '#{code}' and add_time = #{now} and number = #{bonus} order by id desc limit 1"
        (queryRds(isql)).each { |i| bill_id = i['id'] }

        balance = p_balance + bonus
        sendEventPointsNotice  openid, channel, brief, bonus.to_s if mpNotice

        ww_notice = "# 用户积分发放提醒 #{code}\n"
        ww_notice += "#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n\n"
        ww_notice += "**[#{sprintf('%05d',uid)}] #{user_phone} #{user_name}**\n"
        ww_notice += "说明: #{channel}\n"
        ww_notice += "备注: #{brief}\n"
        ww_notice += "详情: #{mark}\n"
        ww_notice += "获得: #{bonus}\n"
        ww_notice += "余额: #{balance.to_f.round(2)}\n\n"
        ww_notice += ">\n"
        send_message_to_bot 'WWBOT_XT', ww_notice, ['@all'] if weworkNotice

        puts "done"
        return bill_id
    end

    puts "[ERROR] something wrong when commit, please check log/rds_api.log"
    return 0

end

def pointsTemplate code='LTR-2024-02-01'
    rec = {}
    rec.store 'code', code
    rec.store 'bonus', 20.24
    rec.store 'channel', "节庆礼券赠送入账" #这里是模板消息的交易门店
    rec.store 'brief', " [ 锦礼辞岁，悦启新运 ] 活动" #这里是模板消息的服务内容
    rec.store 'title', "#{code} 活动赠送" #这里是流水的title
    rec.store 'mark', "#{code} 活动赠送" #这里是流水的mark
    rec.store 'weworkNotice', false
    rec.store 'mpNotice', false
    rec.store 'mpsRequired', false
    return rec
end

#推广LTR-2024-02-01，給每一公众号用户发放积分并推送消息
def event_LTR20240201_I

    rec = pointsTemplate 'LTR-2024-02-01'
    rec.store 'mpNotice', true

    sql = "select uid from tutem_mall.eb_wechat_user where subscribe = 1 and openid is not null and uid in (select uid from tutem_mall.eb_user where status = 1)"
    (queryRds(sql)).each do |q|
        uid = q['uid']
        rec.store 'uid', uid
        awardPointsToUser rec
    end

end

#給没有关注公众号但是下过订单的用户发短信
def event_LTR20240201_II
    sql = "select uid, user_phone from tutem_mall.eb_store_order where paid = 1 and uid not in (select uid from tutem_mall.eb_wechat_user where subscribe = 1 and openid is not null and uid in (select uid from tutem_mall.eb_user where status = 1))"
    uids = []
    phones = []
    phoneList = {}
    (queryRds(sql)).each do |q|
        phone = q['user_phone']
        uid = q['uid']
        uids << uid
        phones << phone
        phoneList.store uid, phone
    end
    phones = phones.uniq #2068
    uids = uids.uniq #2053

    rec = pointsTemplate 'LTR-2024-02-01'

    template = 'ADD_POINTS'
    data = { 'gain_integral' => '20.24', 'integral' => '可换辞岁好礼 totemlife.cn/apps/l/ 祝君家和人旺' }
    weapp_args = {}

    rec = pointsTemplate 'LTR-2024-02-01'
    uids.each do |uid|
        phone = phoneList[uid]
        rec.store 'uid', uid
        bill_id = awardPointsToUser rec
        sendSMS phone, template, data, weapp_args if bill_id > 0
    end

end
sendEventPointsNotice
